﻿using System;
using System.Collections.Generic;
using System.Data.Odbc;
using System.Linq;
using System.Web;

namespace HitachiService.DBAccess
{
    public class ScheduleAccess : DataAccess
    {
        public static void DeleteScheduleById(int id)
        {
            String sql =
                "delete from t_history " +
                "where gid=" + id + ";";
            OdbcConnection conn = GetConnection();
            OdbcCommand cmd = new OdbcCommand(sql, conn);
            conn.Open();
            OdbcDataReader reader = cmd.ExecuteReader();
            conn.Close();
        }

        public static void FreezeScheduleByProjectId(int project_id, int action_type)
        {
            String sql =
                "update t_history set " +
                "freezed=1 " +
                "where project_id=" + project_id + " and action_type=" + action_type;
            OdbcConnection conn = GetConnection();
            OdbcCommand cmd = new OdbcCommand(sql, conn);
            conn.Open();
            OdbcDataReader reader = cmd.ExecuteReader();
            conn.Close();
        }

        public static void FreezeSchedule(ScheduleData data)
        {
            String sql =
                "update t_history set " +
                "freezed=1 " +
                "where project_id=" + data.ProjectId +
                " and action_type=" + data.ActionType +
                " and action_date=" + data.ActionDate +
                " and action_time=" + data.ActionTime;
            OdbcConnection conn = GetConnection();
            OdbcCommand cmd = new OdbcCommand(sql, conn);
            conn.Open();
            OdbcDataReader reader = cmd.ExecuteReader();
            conn.Close();
        }

        public static int SaveSchedule(ScheduleData data)
        {
            if (data.ProjectId != 0)
                FreezeScheduleByProjectId(data.ProjectId, data.ActionType);
            int last_insert_id = 1;
            String sql =
                "insert into t_history " +
                "(project_id,resource_id,action_type,action_date,action_time,freezed) values (" +
                data.ProjectId + ",NULL," + data.ActionType + "," + data.ActionDate + "," + data.ActionTime + "," + "0" +
                ");";
            if (data.ID != 0)
            {
                last_insert_id = 0;
                String resource_str = (data.ResourceId == 0 ? "NULL" : "" + data.ResourceId);
                sql =
                "update t_history set " +
                "project_id=" + data.ProjectId +
                ",resource_id=" + resource_str +
                ",action_type=" + data.ActionType +
                ",action_date=" + data.ActionDate +
                ",action_time=" + data.ActionTime +
                ",freezed=" + "0" +
                " where gid=" + data.ID;
            }
            OdbcConnection conn = GetConnection();
            OdbcCommand cmd = new OdbcCommand(sql, conn);
            conn.Open();
            OdbcDataReader reader = cmd.ExecuteReader();
            if (last_insert_id > 0)
            {
                sql = "select @@IDENTITY;";
                OdbcCommand cmd_last = new OdbcCommand(sql, conn);
                last_insert_id = Convert.ToInt32(cmd_last.ExecuteScalar());
                data.ID = last_insert_id;
            }
            conn.Close();
            return data.ID;
        }

        public static List<ScheduleData> GetScheduleByProject(int project_id)
        {
            String parent_id_str = project_id == 0 ? " is NULL" : "=" + project_id;
            String sql =
                "select " +
                "h.gid, h.project_id, h.resource_id, h.action_type, h.action_date, h.action_time " +
                "from t_history h " +
                "where h.freezed=0 and h.project_id" + parent_id_str;
            List<ScheduleData> result = new List<ScheduleData>();
            OdbcConnection conn = GetConnection();
            OdbcCommand cmd = new OdbcCommand(sql, conn);
            conn.Open();
            OdbcDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                ScheduleData s = new ScheduleData();
                s.ID = ReadInt(reader["gid"]);
                s.ProjectId = ReadInt(reader["project_id"]);
                s.ActionType = ReadInt(reader["action_type"]);
                s.ActionDate = ReadInt(reader["action_date"]);
                s.ActionTime = ReadInt(reader["action_time"]);
                result.Add(s);
            }
            conn.Close();
            return result;
        }

        public static List<ScheduleData> GetSchedulesByParentProject(int parent_project_id)
        {
            String parent_id_str = parent_project_id == 0 ? " is NULL" : "=" + parent_project_id;
            String sql =
                "select " +
                "h.gid, h.project_id, h.resource_id, h.action_type, h.action_date, h.action_time " +
                "from t_history h " +
                "where h.freezed=0 and h.project_id in (select p.gid from t_project p where p.parent_id" + parent_id_str + ")";
            List<ScheduleData> result = new List<ScheduleData>();
            OdbcConnection conn = GetConnection();
            OdbcCommand cmd = new OdbcCommand(sql, conn);
            conn.Open();
            OdbcDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                ScheduleData s = new ScheduleData();
                s.ID = ReadInt(reader["gid"]);
                s.ProjectId = ReadInt(reader["project_id"]);
                s.ActionType = ReadInt(reader["action_type"]);
                s.ActionDate = ReadInt(reader["action_date"]);
                s.ActionTime = ReadInt(reader["action_time"]);
                result.Add(s);
            }
            conn.Close();
            return result;
        }
    }
}